package com.example.maybeaccount.Mode;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;


import com.example.maybeaccount.Entry.User;

/** 数据库控制，对不同数据增删查改 */
public class SqliteDB {
    /**
     * 数据库名
     */
    public static final String DB_NAME = "sqlite_dbname.db";
    /**
     * 数据库版本
     */
    public static final int VERSION = 1;
    private static SqliteDB sqliteDB;
    private SQLiteDatabase db;
    private String sql1;
    private OpenHelper dbHelper;

    private SqliteDB(Context context) {
        dbHelper = new OpenHelper(context, DB_NAME, null, VERSION);
        db = dbHelper.getWritableDatabase();
    }

    /**
     * 获取SqliteDB实例
     */
    public synchronized static SqliteDB getInstance(Context context) {
        if (sqliteDB == null) {
            sqliteDB = new SqliteDB(context);
        }
        return sqliteDB;
    }

    /*******************************************************/
    /*********************** 用户 **************************/
    /*******************************************************/
    /**
     * 将User实例存储到数据库。
     */
    public int  saveUser(User user) {
        if (user != null) {
           /* ContentValues values = new ContentValues();
            values.put("username", user.getUsername());
            values.put("userpwd", user.getUserpwd());
            db.insert("User", null, values);*/

            Cursor cursor = db.rawQuery("select * from User where username=?", new String[]{user.getUsername()});
            if (cursor.getCount() > 0) {
                return -1;
            } else {
                try {
                    db.execSQL("insert into User(username,userpwd,gridpwd,default_meth) values(?,?,?,?) ", new Object[]{user.getUsername(), user.getUserpwd(), user.getGridPwd(),user.getDefaultUnlockMethod()});
                } catch (Exception e) {
                    Log.d("错误", e.getMessage());
                }
                return 1;
            }
        }
        else {
            return 0;
        }
    }

    /**
     * 从数据库读取User信息。
     */
    public List<User> loadUser() {
        List<User> list = new ArrayList<User>();
        Cursor cursor = db
                .query("User", null, null, null, null, null, null);
        if (cursor.moveToFirst()) {
            do {
                User user = new User();
                user.setId(cursor.getInt(cursor.getColumnIndex("id")));
                user.setUsername(cursor.getString(cursor.getColumnIndex("username")));
                user.setUserpwd(cursor.getString(cursor.getColumnIndex("userpwd")));
                user.setGridPwd(cursor.getString(cursor.getColumnIndex("gridpwd")));
                user.setDefaultUnlockMethod(cursor.getInt(cursor.getColumnIndex("default_meth")));
                list.add(user);
            } while (cursor.moveToNext());
        }
        return list;
    }

    /**
     * 更新用户信息
     * @param pwd
     * @param name
     * @param rusername
     * @return
     */
    public int updateUser (String name,String pwd,String rusername) {
        List<User> list = new ArrayList<User>();
        Cursor cursor = db
                .query("User", null, null, null, null, null, null);//读取数据库信息
        if (cursor.getCount()==0){
            return -1;
        }
        else {
            try {
                db.execSQL("update User SET username=?,userpwd=? where username=?",
                        new Object[]{name,pwd,rusername});
            } catch (Exception e) {
                Log.d("错误",e.getMessage());
            }
            return 1;
        }
    }

    /** 更新用户图形密码 */
    public int updateUserGridPswd(User user){

        if(user != null){
            try {
                db.execSQL("update User SET gridpwd=? where username=?",
                        new Object[]{user.getGridPwd(),user.getUsername()});
            } catch (Exception e) {
                Log.d("错误", e.getMessage().toString());
                return 0;
            }
            return 1;
        }else{
            return 0;
        }
    }

    public int updateUserUnlockMethod(User user){
        if(user != null){
            try {
                db.execSQL("update User SET default_meth=? where username=?",
                        new Object[]{user.getDefaultUnlockMethod(),user.getUsername()});
            } catch (Exception e) {
                Log.d("错误", e.getMessage().toString());
                return 0;
            }
            return 1;
        }else{
            return 0;
        }
    }

    public int Quer(String pwd,String name)
    {
        HashMap<String,String> hashmap=new HashMap<String,String>();
        Cursor cursor =db.rawQuery("select * from User where username=?", new String[]{name});

        // hashmap.put("name",db.rawQuery("select * from User where name=?",new String[]{name}).toString());
        if (cursor.getCount()>0)
        {
            Cursor pwdcursor =db.rawQuery("select * from User where userpwd=? and username=?",new String[]{pwd,name});
            if (pwdcursor.getCount()>0)
            {
                return 1;
            }
            else {
                return -1;
            }
        }
        else {
            return 0;
        }
    }

    /*******************************************************/
    /*********************** 账单 **************************/
    /*******************************************************/
    /**
     * 添加账单
     */
    public int saveItem(Item item){
        if (item != null) {
            try {
                /*
                db.execSQL("insert into ITEM(amount,class1,class2,account,year,month,day,hour,minute,time,member,shop,project,remark) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                        new Object[]{item.getAmount(), item.getClass1(), item.getClass2(), item.getAccount(), item.getYear(), item.getMonth(), item.getDay(), item.getHour(), item.getMinute(), item.getStringTime(),item.getMember(), item.getShop(), item.getProject(), item.getRemark()});
                */
                db.execSQL("insert into ITEM(amount,class1,class2,account,time,member,shop" +
                                ",project,remark,mode,payBackTime) values(?,?,?,?,?,?,?,?,?,?,?)",
                        new Object[]{item.getAmount(), item.getClass1(), item.getClass2(), item.getAccount(),
                                item.getStringTime(),item.getMember(), item.getShop(), item.getProject(),
                                item.getRemark(),item.getMode(),item.getStringPayBackTime()});
            } catch (Exception e) {

                Log.d("错误", e.getMessage().toString());
            }
            return 1;
        }
        return 0;
    }

    /**
     * 修改账单
     */
    public int updateItem(Item item){
        if(item != null){
            try {
                db.execSQL("update ITEM SET " +
                                "amount=?,class1=?,class2=?,account=?,time=?,member=?,shop=?," +
                                "project=?,remark=?,mode=?,payBackTime=?"+
                                " where id=?",
                        new Object[]{item.getAmount(), item.getClass1(), item.getClass2(), item.getAccount(),
                                item.getStringTime(),item.getMember(), item.getShop(), item.getProject(),
                                item.getRemark(),item.getMode(),item.getStringPayBackTime(),item.getId()});
            } catch (Exception e) {
                Log.d("错误", e.getMessage().toString());
                return 0;
            }
            return 1;
        }else{
            return 0;
        }
    }

    /**
     * 删除账单
     */
    public int deleteItem(int id){
        if(id >= 0){
            try {
                db.execSQL("delete from ITEM where id=?", new Object[]{id});
            } catch (Exception e) {
                Log.d("错误", e.getMessage().toString());
                return 0;
            }
            return 1;
        }else{
            return 0;
        }
    }

    /**
     * 读所有账单
     */
    public ArrayList<Item> getAllItem() {
        sql1 = "select * from ITEM order by time desc";
        Cursor cursor = db.rawQuery(sql1, null);
        return cursorToList(cursor);
    }
    /**
     * 根据具体 时 间 点 读取账单
     */
    public Item getItemByTime(MyTimer time) {
        sql1 = "select * from ITEM where year =time.year and month=time.month and day=time.day and hour=time.hour and minute=time.minute order by time desc";
        Cursor cursor = db.rawQuery(sql1, null);
        return cursorToList(cursor).get(0);
    }

    /**
     * 根据具体时间段读取账单（具体到天）
     */
    public ArrayList<Item> getItemByDurationDay(MyTimer startTime, MyTimer endTime) {
        ArrayList<Item> itemList = new ArrayList<Item>();
        sql1 = "select * from ITEM where (year< endTime.year OR (year=endTime.year AND month<endTime.month) OR (year=endTime.year AND month=endTime.month AND day<=endTime.day) ) AND (year>startTime.year OR (year=startTime.year AND month>startTime.month) OR (year=startTime.year AND month=startTime.month AND day>=startTime.day)) order by time desc";
        Cursor cursor = db.rawQuery(sql1, null);
        return cursorToList(cursor);
    }

    /**
     * 根据具体时间段读取账单（具体到月）
     */
    public ArrayList<Item> getItemByDurationMonth(MyTimer startTime, MyTimer endTime) {
        ArrayList<Item> itemList = new ArrayList<Item>();
        sql1 = "select * from ITEM where (year<endTime.year OR (year=endTime.year AND month<=endTime.month)) AND (year>startTime.year OR (year=startTime.year AND month>=startTime.month)) order by time desc";
        Cursor cursor = db.rawQuery(sql1, null);
        return cursorToList(cursor);
    }

    /**
     * 根据具体时间段读取账单（具体到年）
     */
    public ArrayList<Item> getItemByDuration(MyTimer startTime, MyTimer endTime) {
        ArrayList<Item> itemList = new ArrayList<Item>();
        sql1 = "select * from ITEM where (year<=endTime.year) AND (year>=startTime.year ) order by time desc";
        Cursor cursor = db.rawQuery(sql1, null);
        return cursorToList(cursor);
    }

    /**
     * 根据账户读取账单
     */
    public ArrayList<Item> getItemByAccount(String accountName) {
        ArrayList<Item> itemList = new ArrayList<Item>();
        sql1 = "select * from ITEM where account=accountName order by time desc";
        Cursor cursor = db.rawQuery(sql1, null);
        return cursorToList(cursor);
    }

    /**
     * 根据成员读取账单
     */
    public ArrayList<Item> getItemByMember(String memberName) {
        ArrayList<Item> itemList = new ArrayList<Item>();
        sql1 = "select * from ITEM where member=" + memberName + " order by time desc";
        Cursor cursor = db.rawQuery(sql1, null);
        return cursorToList(cursor);
    }

    /**
     * 根据一级分类读取账单
     */
    public ArrayList<Item> getItemByCat1(String class1Name) {
        ArrayList<Item> itemList = new ArrayList<Item>();
        sql1 = "select * from ITEM where class1=class1Name";
        Cursor cursor = db.rawQuery(sql1, null);
        return cursorToList(cursor);
    }

    /**
     * 根据二级分类读取账单
     */
    public ArrayList<Item> getItemByCat2(String class2Name) {
        ArrayList<Item> itemList = new ArrayList<Item>();
        sql1 = "select * from ITEM where class2=class2Name";
        Cursor cursor = db.rawQuery(sql1, null);
        return cursorToList(cursor);
    }
    /*******************************************************/
    /*********************** 账户 **************************/
    /*******************************************************/

    /**
     * 添加账户
     */
    public int saveAccount(Account account){
        if(account != null){
            Cursor cursor = db.rawQuery("select * from ACCOUNT where name=?", new String[]{account.getName()});
            if (cursor.getCount() > 0) {
                return -1;
            } else   {
                try {
                    db.execSQL("insert into ACCOUNT(name,class,amount,remark,expend,income) values(?,?,?,?,?,?)",
                            new Object[]{account.getName(),account.getClassification(),account.getAsset(),account.getRemark(),account.getExpend(),account.getIncome()});
                } catch (Exception e) {
                    Log.d("错误", e.getMessage().toString());
                }
                return 1;
            }
        }else{
            return 0;
        }
    }

    /**
     * 更新账户
     */
    public int updateAccount(Account account){
        if(account != null){
            try {
                db.execSQL("update ACCOUNT SET amount=?,income=?,expend=? where name=?",
                        new Object[]{account.getAsset(),account.getIncome(),account.getExpend(),account.getName()});
            } catch (Exception e) {
                Log.d("错误", e.getMessage().toString());
                return 0;
            }
            return 1;
        }else{
            return 0;
        }
    }

    /**
     * 获取所有账户对象
     * @return
     */
    public ArrayList<Account> getAllAccount(){
        ArrayList<Account> accountList = new ArrayList<Account>();
        sql1 = "select * from ACCOUNT";
        Cursor cursor = db.rawQuery(sql1, null);
        if (cursor != null && cursor.getCount() > 0) {
            //移动到首位
            cursor.moveToFirst();
            for (int i = 0; i < cursor.getCount(); i++) {
                String name = cursor.getString(cursor.getColumnIndex("name"));
                String classification = cursor.getString(cursor.getColumnIndex("class"));
                Double amount = cursor.getDouble(cursor.getColumnIndex("amount"));
                String remark = cursor.getString(cursor.getColumnIndex("remark"));

                Account account = new Account(name,classification,amount,remark);
                account.setIncome(cursor.getDouble(cursor.getColumnIndex("income")));
                account.setExpend(cursor.getDouble(cursor.getColumnIndex("expend")));
                account.setId(cursor.getInt(cursor.getColumnIndex("id")));
                accountList.add(account);
                //移动到下一位
                cursor.moveToNext();
            }
        }
        cursor.close();
        //db.close();
        return accountList;
    }

    /**
     * 获取所有账户名
     */
    public ArrayList<String> getAllAccountName(){
        sql1 = "select * from ACCOUNT";
        Cursor cursor = db.rawQuery(sql1, null);
        ArrayList<String> accountNameList = new ArrayList<String>();
        if (cursor != null && cursor.getCount() > 0) {
            //移动到首位
            cursor.moveToFirst();
            for (int i = 0; i < cursor.getCount(); i++) {
                String name = cursor.getString(cursor.getColumnIndex("name"));
                accountNameList.add(name);
                //移动到下一位
                cursor.moveToNext();
            }
        }
        cursor.close();
        //db.close();
        return accountNameList;
    }

    /**
     * 获取指定名称账户
     * @param accountName
     * @return
     */
    public Account getAccountOfName(String accountName){
        sql1 = "select * from ACCOUNT where name=?";
        //"select * from User where username=?"
        Cursor cursor = db.rawQuery("select * from ACCOUNT where name=?", new String[]{accountName});
        if(cursor.moveToFirst()){
            //Cursor cursor = db.rawQuery(sql1, new String[]{accountName});
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String catelog = cursor.getString(cursor.getColumnIndex("class"));
            Double amount = cursor.getDouble(cursor.getColumnIndex("amount"));
            String remark = cursor.getString(cursor.getColumnIndex("remark"));
            Account account = new Account(name,catelog,amount,remark);
            account.setIncome(cursor.getDouble(cursor.getColumnIndex("income")));
            account.setExpend(cursor.getDouble(cursor.getColumnIndex("expend")));
            account.setId(cursor.getInt(cursor.getColumnIndex("id")));
            cursor.close();
            return account;
        }
        Account account = new Account("","",0.0,"");
        return account;

    }





    /*******************************************************/
    /*********************** 辅助 **************************/
    /*******************************************************/

    /**
     * 从cursor读取数据并返回ArrayList
     * @param cursor
     * @return ArrayList<Item>
     */
    private ArrayList<Item> cursorToList(Cursor cursor){
        ArrayList<Item> itemList = new ArrayList<Item>();
        if (cursor != null && cursor.getCount() > 0) {
            //移动到首位
            cursor.moveToFirst();
            for (int i = 0; i < cursor.getCount(); i++) {
                int id = cursor.getInt(cursor.getColumnIndex("id"));
                double amount = cursor.getDouble(cursor.getColumnIndex("amount"));
                String class1 = cursor.getString(cursor.getColumnIndex("class1"));
                String class2 = cursor.getString(cursor.getColumnIndex("class2"));
                String account = cursor.getString(cursor.getColumnIndex("account"));
                //int year = cursor.getInt(cursor.getColumnIndex("year"));
                //int month = cursor.getInt(cursor.getColumnIndex("month"));
                //int day = cursor.getInt(cursor.getColumnIndex("day"));
                //int hour = cursor.getInt(cursor.getColumnIndex("hour"));
                //int minute = cursor.getInt(cursor.getColumnIndex("minute"));
                String time = cursor.getString(cursor.getColumnIndex("time"));
                String member = cursor.getString(cursor.getColumnIndex("member"));
                String shop = cursor.getString(cursor.getColumnIndex("shop"));
                String project = cursor.getString(cursor.getColumnIndex("project"));
                String remark = cursor.getString(cursor.getColumnIndex("remark"));
                String payBackTime = cursor.getString(cursor.getColumnIndex("payBackTime"));
                int mode = cursor.getInt(cursor.getColumnIndex("mode"));

                Item item = new Item();
                item.setAmount(amount);
                item.setClass1(class1);
                item.setClass2(class2);
                item.setAccount(account);
                //item.setTimeManual(year,month,day,hour,minute);
                item.setTimeByString(time);
                item.setMember(member);
                item.setShop(shop);
                item.setProject(project);
                item.setRemark(remark);
                item.setId(id);
                item.setPayBackTimeByString(payBackTime);
                item.setMode(mode);

                itemList.add(item);
                //移动到下一位
                cursor.moveToNext();
            }
        }
        return itemList;
    }

}